<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h2 data-lake-id="3c4e0a25" id="3c4e0a25"><span data-lake-id="u8aed3802" id="u8aed3802">典型回答</span></h2>
  <p data-lake-id="ub234e398" id="ub234e398"><br></p>
  <p data-lake-id="u1db60c32" id="u1db60c32"><span data-lake-id="u67504645" id="u67504645">在MySQL中，使用like进行模糊查询，在一定情况下是无法使用索引的。如下所示：</span></p>
  <p data-lake-id="u54316be7" id="u54316be7"><br></p>
  <ul list="ua8fd5f43">
   <li fid="u53353dfc" data-lake-id="u450c66f8" id="u450c66f8"><span data-lake-id="u53477490" id="u53477490">当like值前后都有匹配符时</span><code data-lake-id="ub2e679fc" id="ub2e679fc"><span data-lake-id="u7b979c33" id="u7b979c33">%abc%</span></code><span data-lake-id="u0290de6a" id="u0290de6a">，无法使用索引</span></li>
  </ul>
  <p data-lake-id="udf2cfa86" id="udf2cfa86"><br></p>
  <pre lang="java"><code>
EXPLAIN SELECT * FROM `test` WHERE `name` LIKE '%abc%' ;

+----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref    | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+
| 1  | SIMPLE      | test  | &lt;null&gt;     | ALL  | &lt;null&gt;        | &lt;null&gt; | &lt;null&gt;  | &lt;null&gt; | 19820 | 11.11    | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+
</code></pre>
  <p data-lake-id="u66f398cd" id="u66f398cd"><br></p>
  <ul list="ua71e9b0a">
   <li fid="u04c511cc" data-lake-id="u63a1c4f7" id="u63a1c4f7"><span data-lake-id="ua41220db" id="ua41220db">当like值前有匹配符时</span><code data-lake-id="u79e3f63c" id="u79e3f63c"><span data-lake-id="u8de1a9b4" id="u8de1a9b4">%abc</span></code><span data-lake-id="uad5a7b3e" id="uad5a7b3e">，无法使用索引</span></li>
  </ul>
  <p data-lake-id="u36ac1262" id="u36ac1262"><br></p>
  <pre lang="java"><code>
EXPLAIN SELECT * FROM `test` WHERE `name` LIKE '%abc' ;

+----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref    | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+
| 1  | SIMPLE      | test  | &lt;null&gt;     | ALL  | &lt;null&gt;        | &lt;null&gt; | &lt;null&gt;  | &lt;null&gt; | 19820 | 11.11    | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+
</code></pre>
  <p data-lake-id="ub773fd39" id="ub773fd39"><br></p>
  <ul list="u2ab449a1">
   <li fid="u9f1e2008" data-lake-id="u3c6bed67" id="u3c6bed67"><span data-lake-id="uc5507bf7" id="uc5507bf7">当like值后有匹配符时'abc%'，可以使用索引</span></li>
  </ul>
  <p data-lake-id="u85d4cc2f" id="u85d4cc2f"><br></p>
  <pre lang="java"><code>
EXPLAIN SELECT * FROM `test` WHERE `name` LIKE 'abc%' ;

+----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref    | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+
| 1  | SIMPLE      | test  | &lt;null&gt;     | range | idx_name      | idx_name | 153     | &lt;null&gt; | 200  | 100.0    | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+
</code></pre>
  <p data-lake-id="ud19d8357" id="ud19d8357"><br></p>
  <p data-lake-id="ubed161b3" id="ubed161b3"><span data-lake-id="u3d65fb6f" id="u3d65fb6f">那么，</span><code data-lake-id="ub6db9239" id="ub6db9239"><span data-lake-id="u6e2e43f5" id="u6e2e43f5">like %abc</span></code><span data-lake-id="u7aed73b7" id="u7aed73b7">真的无法优化了吗？</span></p>
  <p data-lake-id="u802c6c1c" id="u802c6c1c"><span data-lake-id="uda0c9088" id="uda0c9088"><br>
    我们之所以会使用</span><code data-lake-id="u9aa39348" id="u9aa39348"><span data-lake-id="ub697e00b" id="ub697e00b">%abc</span></code><span data-lake-id="u3d040551" id="u3d040551">来查询说明表中的name可能包含以abc结尾的字符串，如果以</span><code data-lake-id="u0d192744" id="u0d192744"><span data-lake-id="ub96c854c" id="ub96c854c">abc%</span></code><span data-lake-id="u75b49096" id="u75b49096">说明有以abc开头的字符串。</span></p>
  <p data-lake-id="ufc743d1a" id="ufc743d1a"><span data-lake-id="ufe4cab78" id="ufe4cab78"><br>
    假设我们要向表中的name写入123abc，我们可以将这一列反转过来，即cba321插入到一个冗余列v_name中，并为这一列建立索引：</span></p>
  <p data-lake-id="u5d40a2a6" id="u5d40a2a6"><br></p>
  <pre lang="java"><code>
ALTER TABLE `test` ADD COLUMN `v_name` VARCHAR(50) NOT NULL DEFAULT ''; //为test表新增v_name列
ALTER TABLE `test` ADD INDEX `idx_v_name`(`v_name`); //为v_name列添加索引
INSERT INTO `test`(`id`,`name`,`v_name`)VALUES(1,'123abc','cba321'); //这里不但要写name，也要写v_name
</code></pre>
  <p data-lake-id="u846ffbe0" id="u846ffbe0"><br></p>
  <p data-lake-id="u71573641" id="u71573641"><span data-lake-id="u8ba23cec" id="u8ba23cec">接下来在查询的时候，我们就可以使用v_name列进行模糊查询了</span></p>
  <p data-lake-id="uf9b1325a" id="uf9b1325a"><br></p>
  <pre lang="java"><code>
SELECT * FROM `test` WHERE `v_name` LIKE 'cba%'; //相当于反向查询匹配出了name=123abc的行
</code></pre>
  <p data-lake-id="uefb86a80" id="uefb86a80"><br></p>
  <p data-lake-id="u7f4f47a9" id="u7f4f47a9"><span data-lake-id="u60910efb" id="u60910efb">当然这样看起来有点麻烦，表中如果已经有了很多数据，还需要利用update语句反转name到v_name中，如果数据量大了（几百万或上千万条记录）更新一下v_name耗时也比较长，同时也会增大表空间。</span></p>
  <p data-lake-id="uf7eabd99" id="uf7eabd99"><br></p>
  <pre lang="java"><code>
UPDATE `test` SET `v_name` = REVERSE(`name`);
</code></pre>
  <p data-lake-id="u36bf4c53" id="u36bf4c53"><br></p>
  <p data-lake-id="uef415c39" id="uef415c39"><span data-lake-id="u62ac3cb1" id="u62ac3cb1">幸运的是在MySQL5.7.6之后，新增了虚拟列功能（如果不是&gt;=5.7.6，只能用上面的土方法）。为一个列建立一个虚拟列，并为虚拟列建立索引，在查询时where中like条件改为虚拟列，就可以使用索引了。</span></p>
  <p data-lake-id="u6a616188" id="u6a616188"><br></p>
  <pre lang="java"><code>
ALTER TABLE `test` ADD COLUMN `v_name` VARCHAR(50) GENERATED ALWAYS AS (REVERSE(`name`)) VIRTUAL; //创建虚拟列
ALTER TABLE `test` ADD INDEX `idx_name_virt`(`v_name`); //为虚拟列v_name列添加索引
</code></pre>
  <p data-lake-id="u20de1c7a" id="u20de1c7a"><br></p>
  <p data-lake-id="u3cf73b95" id="u3cf73b95"><span data-lake-id="u1adeb399" id="u1adeb399">我们再进行查询，就会走索引了</span></p>
  <p data-lake-id="ue861601b" id="ue861601b"><br></p>
  <pre lang="java"><code>
EXPLAIN SELECT * FROM `test` WHERE `v_name` LIKE 'cba%';

+----+-------------+-------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref    | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| 1  | SIMPLE      | test  | &lt;null&gt;     | range | idx_name_virt | idx_name_virt | 153     | &lt;null&gt; | 200  | 100.0    | Using where |
+----+-------------+-------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
</code></pre>
  <p data-lake-id="uebd00e62" id="uebd00e62"><br></p>
  <p data-lake-id="uec30b6ae" id="uec30b6ae"><span data-lake-id="u9e9f4104" id="u9e9f4104">当然如果你要查询</span><code data-lake-id="u242d9fcc" id="u242d9fcc"><span data-lake-id="ubb4f3f66" id="ubb4f3f66">like 'abc%'</span></code><span data-lake-id="u54e52f2b" id="u54e52f2b">和</span><code data-lake-id="u619cdf25" id="u619cdf25"><span data-lake-id="uc06c8b2e" id="uc06c8b2e">like '%abc'</span></code><span data-lake-id="ub70d57f0" id="ub70d57f0">，你只需要使用一个union</span></p>
  <p data-lake-id="u9be9a5f9" id="u9be9a5f9"><br></p>
  <pre lang="java"><code>
EXPLAIN SELECT * FROM `test` WHERE `v_name` LIKE 'cba%' //第一部分查询的是虚拟列
UNION SELECT * FROM `test` WHERE `name` LIKE 'abc%'; //第二部分查询的是原name列

+--------+--------------+------------+------------+-------+---------------+---------------+---------+--------+--------+----------+-----------------------+
| id     | select_type  | table      | partitions | type  | possible_keys | key           | key_len | ref    | rows   | filtered | Extra                 |
+--------+--------------+------------+------------+-------+---------------+---------------+---------+--------+--------+----------+-----------------------+
| 1      | PRIMARY      | test       | &lt;null&gt;     | range | idx_name_virt | idx_name_virt | 153     | &lt;null&gt; | 200    | 100.0    | Using where           |
| 2      | UNION        | test       | &lt;null&gt;     | range | idx_name      | idx_name      | 153     | &lt;null&gt; | 200    | 100.0    | Using index condition |
| &lt;null&gt; | UNION RESULT | &lt;union1,2&gt; | &lt;null&gt;     | ALL   | &lt;null&gt;        | &lt;null&gt;        | &lt;null&gt;  | &lt;null&gt; | &lt;null&gt; | &lt;null&gt;   | Using temporary       |
+--------+--------------+------------+------------+-------+---------------+---------------+---------+--------+--------+----------+-----------------------+
</code></pre>
  <p data-lake-id="u9869a7e9" id="u9869a7e9"><br></p>
  <p data-lake-id="u70fe63f8" id="u70fe63f8"><span data-lake-id="u311c95f4" id="u311c95f4">可以看到，除了union result合并俩个语句，另外俩个查询都已经走索引了。如果你只想需要查询name，甚至可以使用覆盖索引进一步提升性能</span></p>
  <p data-lake-id="uc5f6e0b7" id="uc5f6e0b7"><br></p>
  <pre lang="java"><code>
EXPLAIN SELECT REVERSE(`v_name`) `test` WHERE `v_name` LIKE 'cba%' //第一部分查询的是虚拟列,注意把v_name反转过来就拿到name的值了
UNION SELECT `name` FROM `test` WHERE `name` LIKE 'abc%'; //第二部分查询的是原name列

+--------+--------------+------------+------------+-------+---------------+---------------+---------+--------+--------+----------+--------------------------+
| id     | select_type  | table      | partitions | type  | possible_keys | key           | key_len | ref    | rows   | filtered | Extra                    |
+--------+--------------+------------+------------+-------+---------------+---------------+---------+--------+--------+----------+--------------------------+
| 1      | PRIMARY      | test       | &lt;null&gt;     | range | idx_name_virt | idx_name_virt | 153     | &lt;null&gt; | 200    | 100.0    | Using where; Using index |
| 2      | UNION        | test       | &lt;null&gt;     | range | idx_name      | idx_name      | 153     | &lt;null&gt; | 200    | 100.0    | Using where; Using index |
| &lt;null&gt; | UNION RESULT | &lt;union1,2&gt; | &lt;null&gt;     | ALL   | &lt;null&gt;        | &lt;null&gt;        | &lt;null&gt;  | &lt;null&gt; | &lt;null&gt; | &lt;null&gt;   | Using temporary          |
+--------+--------------+------------+------------+-------+---------------+---------------+---------+--------+--------+----------+--------------------------+
</code></pre>
  <p data-lake-id="u60fcc26a" id="u60fcc26a"><br></p>
  <p data-lake-id="uc7447149" id="uc7447149"><span data-lake-id="u3c397d1e" id="u3c397d1e">虚拟列可以指定为VIRTUAL或STORED，VIRTUAL不会将虚拟列存储到磁盘中，在使用时MySQL会现计算虚拟列的值，STORED会存储到磁盘中，相当于我们手动创建的冗余列。所以：如果你的磁盘足够大，可以使用STORED方式，这样在查询时速度会更快一些。</span></p>
  <p data-lake-id="ub5d0e706" id="ub5d0e706"><br></p>
  <p data-lake-id="ub80730a1" id="ub80730a1"><span data-lake-id="ua35a6de4" id="ua35a6de4">如果你的数据量级较大，不使用反向查询的方式耗时会非常高。你可以使用如下sql测试虚拟列的效果：</span></p>
  <p data-lake-id="ub507be94" id="ub507be94"><br></p>
  <pre lang="java"><code>
//建表
CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  INDEX idx_name (name)
) CHARACTER SET utf8;

//创建一个存储过程，向test表中写入2000000条数据，200条数据中abc字符前包含一些随机字符（用于测试like '%abc'的情况），200条数据中abc字符后包含一些随机字符（用于测试like 'abc%'的情况），其余行不包含abc字符
DELIMITER //

CREATE PROCEDURE InsertTestData()
BEGIN
  DECLARE i INT DEFAULT 1;
  
  WHILE i &lt;= 2000000 DO
    IF i &lt;= 200 THEN
      SET @randomPrefix1 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), CHAR(FLOOR(RAND() * 26) + 48));
      SET @randomString1 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), CHAR(FLOOR(RAND() * 26) + 48));
      SET @randomName1 = CONCAT(@randomPrefix1, @randomString1, 'abc');
      INSERT INTO test (name) VALUES (@randomName1);
    ELSEIF i &lt;= 400 THEN
      SET @randomString2 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), CHAR(FLOOR(RAND() * 26) + 48));
      SET @randomName2 = CONCAT('abc', @randomString2);
      INSERT INTO test (name) VALUES (@randomName2);
    ELSE
      SET @randomName3 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), CHAR(FLOOR(RAND() * 26) + 48));
      INSERT INTO test (name) VALUES (@randomName3);
    END IF;
    
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;

//调用存储过程，这里执行的会很慢
call InsertTestData();

//建立虚拟列
alter table test add column `v_name` varchar(50) generated always as (reverse(name));
//为虚拟列创建索引
alter table test add index `idx_name_virt`(v_name);

//使用虚拟列模糊查询
select * from test where v_name like 'cba%'
union
select * from test where name like 'abc%'

//不使用虚拟列模糊查询
select * from test where name like 'abc%'
union
select * from test where name like '%abc'
</code></pre>
 </body>
</html>